#!/bin/bash

###############################################################################
#
# filename sqlite3.sh
# author GabrielBinit
# creatTime 2015.11.30
#
# execute sqlite3 sql
#
###############################################################################

DISK_DIR='/opt/fusionstack/data/chunk/';
TEST_DIR='/tmp/'

table_test() {
    database='test.db'
    datadir=$TEST_DIR
    idx=0
    j=0
    data='0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPTRSTUVWXYZ'

    for i in {1..5};do
        data=$data+$data
    done

    if [ ! -d $datadir ]; then
        mkdir -p $datadir
    fi

    if [ ! -f $datadir$database ]; then
        touch $datadir$database
    fi

    echo "$datadir$database"
    cmd="sqlite3 $datadir$database 'create table test(id INTEGER PRIMARY KEY, data TEXT)'"
    echo "$cmd" | sh

    while true; do
        echo "$idx"

        let j=0;
        while [ $j -lt 100 ]; do
            cmd="sqlite3 $datadir$database 'insert into test values($idx, \"$data\")'"
            echo "$cmd" | sh
            let j=$j+1;
            let idx=$idx+1;
        done

        cmd="sqlite3 $datadir$database 'delete from test'"
        echo "$cmd" | sh
    done
}

table_list() {
    db=$1

    if [ $db == "all" ]; then
        database=$DISK_DIR

        if [ ! -d $database ]; then
            echo "$database not exists"
            exit 1
        fi

        for db in `ls $database`; do
            is_db=`expr match $db "[0-9]\.db$"`
            if [ ${is_db} -gt 0 ]; then
                echo "$db"
                cmd="sqlite3 $database$db '.tables'"
                echo "$cmd" | sh
                echo ""
            else
                #echo "$db is not a dbfile, continue"
                #echo ""
                continue
            fi
        done
    else
        cmd="sqlite3 $db '.tables'"
        echo "$cmd" | sh
    fi
}

table_listcolumn() {
    db=$1
    table=$2

    if [ -z "$table" ]; then
            echo "table must specify"
            exit 1
    fi

    if [ $db == "all" ]; then
        database=$DISK_DIR

        if [ ! -d $database ]; then
            echo "$database not exists"
            exit 1
        fi

        for db in `ls $database`; do
            is_db=`expr match $db "[0-9]\.db$"`
            if [ ${is_db} -gt 0 ]; then
                echo "$db"
                cmd="sqlite3 $database$db 'PRAGMA table_info($table)'"
                echo "$cmd" | sh
                echo ""
            else
                #echo "$db is not a dbfile, continue"
                #echo ""
                continue
            fi
        done
    else
        cmd="sqlite3 $db 'PRAGMA table_info($table)'"
        echo "$cmd" | sh
    fi
}

table_addcolumn() {
    db=$1
    table=$2
    column=$3
    coltype=$4
    defvalue=$5

    if [ -z "$table" ]; then
            echo "table must specify"
            exit 1
    fi

    if [ $db == "all" ]; then
        database=$DISK_DIR

        if [ ! -d $database ]; then
            echo "$database not exists"
            exit 1
        fi

        if [ $# != 5 ]; then
            echo "Please check your column <column type value>"
            exit 1
        fi

        for db in `ls $database`; do
            is_db=`expr match $db "[0-9]\.db$"`
            if [ ${is_db} -gt 0 ]; then
                echo "$db table $table add column ($column, $coltype, $defvalue)"
                cmd="sqlite3 $database$db 'ALTER TABLE \"$table\"  ADD COLUMN \"$column\" $coltype DEFAULT($defvalue)'"
                echo "$cmd" | sh
                echo ""
            else
                #echo "$db is not a dbfile, continue"
                continue
            fi
        done
    else
        cmd="sqlite3 $db 'ALTER TABLE \"$table\"  ADD COLUMN \"$3\" $4 DEFAULT($5)'"
        echo "$cmd" | sh
    fi
}

table_select() {
    db=$1
    table=$2

    if [ -z "$table" ]; then
            echo "table must specify"
            exit 1
    fi

    if [ $db == "all" ]; then
        database=$DISK_DIR

        if [ ! -d $database ]; then
            echo "$database not exists"
            exit 1
        fi

        for db in `ls $database`; do
            is_db=`expr match $db "[0-9]\.db$"`
            if [ ${is_db} -gt 0 ]; then
                echo "$db"
                columns=`sqlite3 $database$db "PRAGMA table_info($table)" | \
                    awk -F "|" 'BEGIN{ORS=""; idx=0; printf("|        ");}\
                    {printf("%s", \$2); \
                    if(idx==0){printf("         |");}\
                    else if(idx==2) {printf("|     ")}\
                    else if (idx==3){printf("      |")}\
                    else{printf("|")} \
                    idx++;}'`
                if [ "$columns" != "|        " ]; then
                    echo "$columns"
                fi
                cmd="sqlite3 $database$db 'SELECT * FROM \"$table\"'"
                echo "$cmd" | sh
                echo ""
            else
                #echo "$db is not a dbfile, continue"
                #echo ""
                continue
            fi
        done
    else
        columns=`sqlite3 $db "PRAGMA table_info($table)" | \
            awk -F "|" 'BEGIN{ORS=""; idx=0; printf("|        ");}\
                {printf("%s", \$2); \
                if(idx==0){printf("        |");}\
                else if(idx==2) {printf("|     ")}\
                else if (idx==3){printf("     |")}\
                else{printf("|")} \
                idx++;}'`
        echo "$columns"
        cmd="sqlite3 $db 'SELECT * FROM \"$table\"'"
        echo "$cmd" | sh
    fi
}

table_count() {
    db=$1
    table=$2
    total=0

    if [ -z "$table" ]; then
            echo "table must specify"
            exit 1
    fi

    if [ $db == "all" ]; then
        database=$DISK_DIR

        if [ ! -d $database ]; then
            echo "$database not exists"
            exit 1
        fi

        for db in `ls $database`; do
            is_db=`expr match $db "[0-9]\.db$"`
            if [ ${is_db} -gt 0 ]; then
                echo "$db"
                cmd="sqlite3 $database$db 'select count(*) from $table'"
                count=`echo "$cmd" | sh`
                if [ ! -z "$count" ]; then
                    let total=$total+$count
                    echo "$count"
                else
                    echo "0"
                fi
                echo ""
            else
                #echo "$db is not a dbfile, continue"
                #echo ""
                continue
            fi
        done
        echo "total : $total"
    else
        cmd="sqlite3 $db 'select count(*) from $table'"
        echo "$cmd" | sh
    fi
}

table_rename() {
    db=$1
    table=$2
    table_new=$3

    if [ $db == "all" ]; then
        database=$DISK_DIR

        if [ ! -d $database ]; then
            echo "$database not exists"
            exit 1
        fi

        for db in `ls $database`; do
            is_db=`expr match $db "[0-9]\.db$"`
            if [ ${is_db} -gt 0 ]; then
                echo "$db rename $table to $table_new"
                cmd="sqlite3 $database$db 'ALTER TABLE $table RENAME TO $table_new;'"
                echo "$cmd" | sh
                echo ""
            else
                #echo "$db is not a dbfile, continue"
                continue
            fi
        done
    else
        cmd="sqlite3 $db 'ALTER TABLE $table RENAME TO $table_new;'"
        echo "$cmd" | sh
    fi
}

table_backup() {
    db=$1
    table=$2

    if [ $db == "all" ]; then
        database=$DISK_DIR

        if [ ! -d $database ]; then
            echo "$database not exists"
            exit 1
        fi

        for db in `ls $database`; do
            is_db=`expr match $db "[0-9]\.db$"`
            if [ ${is_db} -gt 0 ]; then
                table_bak=${table}_bak
                echo "$db backup $table to $table_bak"
                sql=`sqlite3 $database$db ".schema $table"`
                cmd="sqlite3 $database$db '${sql/$table/$table_bak}'"
                echo "$cmd" | sh
                cmd="sqlite3 $database$db 'insert into $table_bak select * from $table;'"
                echo "$cmd" | sh
                echo ""
            else
                #echo "$db is not a dbfile, continue"
                continue
            fi
        done
    else
        table_bak=${table}_bak
        sql=`sqlite3 $db ".schema $table"`
        cmd="sqlite3 $db '${sql/$table/$table_bak}'"
        echo "$cmd" | sh
        cmd="sqlite3 $db 'insert into $table_bak select * from $table;'"
        echo "$cmd" | sh
    fi
}

usage()
{
    echo "Usage: $0 [OPTIONS]"
    echo
    echo "[-d <db>]"
    echo "-o listtab"
    echo
    echo "-t table"
    echo "-o listcol"
    echo "-o addcol <column> <type> <value>"
    echo "-o select"
    echo "-o count"
    echo "-o rename <name>"
    echo "-o backup"

    exit 1
}

main() {
    if [ $# -lt 1 ]; then
        usage
    fi

    while getopts d:t:o:h options; do
        case $options in
            d)
                db="$OPTARG"
                ;;
            t)
                table="$OPTARG"
                ;;
            o)
                opt="$OPTARG"
                shift $(($OPTIND - 1))
                column=$*
                ;;
            h)
                usage
                ;;
            \?)
                usage
                ;;
        esac
    done

    if [ -z "$db" ]; then
        db="all"
    fi

    if [ "$opt" == "listtab" ]; then
        table_list $db;
    elif [ "$opt" == "test" ]; then
        table_test;
    elif [ "$opt" == "listcol" ]; then
        table_listcolumn $db $table;
    elif [ "$opt" == "addcol" ]; then
        table_addcolumn $db $table $column;
    elif [ "$opt" == "select" ]; then
        table_select $db $table;
    elif [ "$opt" == "count" ]; then
        table_count $db $table;
    elif [ "$opt" == "rename" ]; then
        table_rename $db $table $column;
    elif [ "$opt" == "backup" ]; then
        table_backup $db $table;
    else
        usage
    fi
}

main $*;
